<?php
########################################################
# DMS Donations Management System v1.0                 #
# created by:  Isaac Sabas                             #
#			   Nikko Reyes							   #
#			   Miko Tiamwatt						   #
#                                                      #
# De La Salle Philippines                              #
# All Rights Reserved DLSP Copyright 2009              #
########################################################

# INFORMATION:
# AgentHandle.php 
# 	- this is the class that handles the connection of the transaction window to the database
#	- all MODIFY, SELECT, and DELETE functions are housed in this class
# 	
# FUNCTION INFORMATION:
#	1. CONSTRUCTOR:
#		- sets up the database connection
#	2. LOAD INFORMATION DATA:
#		a. getAgent (agentId) - gets the profile of the agent with the corresponding id
#		b. getTimeInOut (agentId) - gets all the time in and time out of the agent
#		c. getAgentPassword (agentId) - gets the password of the agent
#		d. getCallTask (callId) - gets a call task detail
#		e. getCallTaskList (agentId) - gets the call task list of an agent
#		f. getAllCallTaskList () - gets all the call tasks assigned to all telemarketers that are still active (for administrative tasks)
#	3. STORE DATA:
#		a. newAgent () - adds a new agent
#		b. agentTimeIn (agentId) - adds the agent into the timein database (don't use)
#		c. agentLogIn (agentId) - adds the agent into the timein database
#		d. addCallTask (valuesArray) - adds a call task to an agent
# 	4. MODIFY DATA:
#		a. setProfileValue (item, value, agentId) - change the value of the corresponding item of an agent's profile 
# 		b. agentTimeOut (timeId) - modifies the timein entry and input the timeout time of the agent (don't use)
#		c. agentLogOut (timeId) - modifies the timein entry and input the timeout time of the agent
# 		d. disableAgent (agentId) - disables the account of the agent 
#		e. finishCall (callId, ticketId) - updates the call task as complete and associates it with the corresponding call ticket


class AgentHandle{
# FOR CLASS MANAGEMENT #
# 	The following variables are set to uppercase. This means that they are    
#	class monitoring variables.			

	# FOR DETERMINING THAT THE CLASS HAS ALREADY BEEN INSTANTIATED #
	# boolean variable [ value is NULL when class has not been instantiated ]
		private $CLASS_INSTANTIATED = NULL;							              

	# CLASS MESSAGE_HANDLE HOLDER #
	# handles the error and notification messages of the class to notify the user 
	# to capture all sql and system error, notifications and logs 
		public $MESSAGE_HANDLE = NULL;

	# DB_HANDLE INSTANCE HOLDER #
	# database connection handler [ MYSQL ]
	# instance of the database handle is placed here for the system to use the database with ease 
		public $DB_HANDLE = NULL;
	
# CLASS CONSTANT VARIABLES #
# this is for class management purposes
	private $errorArray = array("DB_NOTCONNECTED" => "Not connected to database.",
							    "DB_QUERYERROR" => "Problem with query",
							    "DB_INVALIDFIELD" => "Invalid field being accessed."
							   );		
							   
	# permissions: #
	# 1 = admin
	# 2 = team lead
	# 3 = telemarketer					

########################################
# TRANSACTION HANDLE CLASS CONSTRUCTOR #
########################################
	public function AgentHandle(){
		# include the needed / required library
			require_once('DBHandle.php');
		# instantiate the DBHandle and assign it to the class attribute DB_HANDLE	
			$databaseConnection = new DBHandle();
			$this->DB_HANDLE = $databaseConnection->DB_HANDLE;
	}	

##############################	
# LOAD SYSTEM DATA FUNCTIONS #	
##############################			
	public function getAgent ($agentId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$agentId = addslashes($agentId);
				# prepare the statement
					$statement = "SELECT * FROM agents WHERE agent_id = '$agentId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:getAgent]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
					
						# get the entry values
							$resultArray = mysql_fetch_array($result);
						# return the values											
							return $resultArray;
					}
			//}
	}
	
		public function getProfileItem ($item, $agentId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$agentId = addslashes($agentId);
					$item = addslashes($item);
				# prepare the statement
					$statement = "SELECT $item FROM agents WHERE agent_id = '$agentId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:getProfileItem]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
						
						# get the entry values
							$resultArray = mysql_fetch_assoc($result);
						# return the values											
							return $resultArray[$item];
					}		
			//}
	}
	
	public function getTimeInOut ($agentId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$agentId = addslashes($agentId);
				# prepare the statement
					$statement = "SELECT * FROM agent_timein WHERE agent_id = '$agentId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:getTimeInOut]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
					
						# get the entry values
							$resultArray = mysql_fetch_array($result);
						# return the values											
							return $resultArray;
					}
			//}
	}
	
	public function getAgentPassword ($agentId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$agentId = addslashes($agentId);
				# prepare the statement
					$statement = "SELECT agent_password FROM agents WHERE agent_id = '$agentId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:getAgent]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
					
						# get the entry values
							$resultArray = mysql_fetch_array($result);
						# return the values											
							return $resultArray;
					}
			//}
	}
	
	public function getCallTask ($callId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$callId = addslashes($callId);
					$resultArray = array();
				# prepare the statement
					$statement = "SELECT * FROM call_list WHERE call_id = '$callId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:getCallTask]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
					
						# get the entry values
							$resultArray = mysql_fetch_array($result);
						# return the values											
							return $resultArray;
					}
			//}
	}
	
	public function getCallTaskList ($agentId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$agentId = addSlashes($agentId);
					$resultArray = array();
				# prepare the statement
					$statement = "SELECT * FROM call_list WHERE agent_id = '$agentId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:getCallTaskList]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
						
						# get the entry values
							while ($row = mysql_fetch_assoc($result)){ 
								$resultArray[] = $row; 
							}
						# return the values											
							return $resultArray;
					}		
			//}
	}
	
	public function getAllTaskList ($callStatus) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				$callStatus = addslashes($callStatus);
				# prepare the statement
					$statement = "SELECT * FROM call_list WHERE call_status = '$callStatus'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:getCallTaskList]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
						
						# get the entry values
							while ($row = mysql_fetch_assoc($result)){ 
								$resultArray[] = $row; 
							}
						# return the values											
							return $resultArray;
					}		
			//}
	}

###############################	
# STORE SYSTEM DATA FUNCTIONS #	
###############################	
	
	# the store functions stores the values to the class' attributes and then connects to the database to store the value
	# it is already assumed that the script that will provide the necessary input validation and error checking
	# the only security the setters provide is the addslashes() function to protect SQL injection from happening
	
	public function addAgent ($valuesArray) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/		
				# prepare the statement 
					$statement = "INSERT INTO agents (				
							agent_username,
							agent_password,
							agent_email,
							agent_phone,
							agent_lastname,
							agent_firstname,
							agent_middlename,
							agent_gender,
							agent_birthday,
							agent_permissions,
							agent_team
						) 
						VALUES (
							'{$valuesArray['agent_username']}',
							'{$valuesArray['agent_password']}',
							'{$valuesArray['agent_email']}',
							'{$valuesArray['agent_phone']}',
							'{$valuesArray['agent_lastname']}',
							'{$valuesArray['agent_firstname']}',
							'{$valuesArray['agent_middlename']}',
							'{$valuesArray['agent_gender']}',
							'{$valuesArray['agent_birthday']}',
							'{$valuesArray['agent_permissions']}',
							'{$valuesArray['agent_team']}'
						)";
					
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:addAgent]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
			
						# return the ticket_id of the newly inserted client										
							return mysql_insert_id();
					}	
			//}
	}
	
	public function agentTimeIn ($agentId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/		
				$agentId = addslashes($agentId);
				$timein = time();
				$date = date("Y-m-d", time()); // NOTE: PLEASE CONVERT TO DATE FORMAT
				# prepare the statement 
					$statement = "INSERT INTO agent_timein (				
							time_in,
							time_indate,
							agent_id
						) 
						VALUES (
							'{$timein}',
							'{$date}',
							'{$agentId}'
						)";
					
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:agentTimeIn]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
			
						# return the ticket_id of the newly inserted client										
							return mysql_insert_id();
					}	
			//}
	}
	
	public function agentLogIn ($agentId) {	
		$agentId = addslashes($agentId);
		$timein = time();
		$date = date("Y-m-d", time()); // NOTE: PLEASE CONVERT TO DATE FORMAT
		# prepare the statement 
			$statement = "INSERT INTO agent_timein (				
					time_in,
					time_indate,
					agent_id
				) 
				VALUES (
					'{$timein}',
					'{$date}',
					'{$agentId}'
				)";
					
		# query & check if error has occured
			if(!$result = mysql_query($statement)) {
				return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:agentTimeIn]';
			}
			else {
				# debugging purposes <remove on production>	
					//echo "updated the database";
	
				# return the ticket_id of the newly inserted client										
					return mysql_insert_id();
			}	
	}
	
	public function addCall ($valuesArray, $agentId, $clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/		
				$time = time();
				$date = date("Y-m-d", time());
				# prepare the statement 
					$statement = "INSERT INTO call_list (				
							agent_id,
							client_id,
							call_tocalldate,
							call_tocalltime,
							call_purpose,
							call_priority,
							call_status
						) 
						VALUES (
							'{$agentId}',
							'{$clientId}',
							'{$date}',
							'{$time}',
							'{$valuesArray['call_purpose']}',
							'{$valuesArray['call_priority']}',
							'0'
						)";
					
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:addAgent]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
			
						# return the ticket_id of the newly inserted client										
							return mysql_insert_id();
					}	
			//}
	}
		
#########################
# MODIFY DATA FUNCTIONS #
#########################
	
	public function setProfileValue ($item, $value, $agentId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {	*/			
				$item = addslashes($item);
				$value = addslashes($value);
				$agentId = addslashes($agentId);
				# check if the item to be modified is the client_id. if it is, reject the modification
					if ($item == 'time_id') {
						# set the error ID to the class attribute
							$this->MESSAGE_HANDLE = 'DB_INVALIDFIELD';
				
						return $this->MESSAGE_HANDLE;
					}
					else {
						# prepare the statement
							$statement = "UPDATE agents SET $item = '$value' WHERE agent_id = '$agentId'";	
						# query & check if error has occured
							if(!$result = mysql_query($statement)) {
								return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:setProfileValue]';
							}
							else {
								# debugging purposes <remove on production>	
									//echo "updated the database";
				
								# return the values											
									return true;
							}	
					}	
			//}
	}
	
	public function agentTimeOut ($timeId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/				
				$timeOut = time();
				# check if the item to be modified is the client_id. if it is, reject the modification
					if ($item == 'time_id') {
						# set the error ID to the class attribute
							$this->MESSAGE_HANDLE = 'DB_INVALIDFIELD';
				
						return $this->MESSAGE_HANDLE;
					}
					else {
						# prepare the statement
							$statement = "UPDATE agent_timein SET time_out = '$timeOut', time_outdate = '$dateOut' WHERE time_id = '" . $timeId . "'";	
						# query & check if error has occured
							if(!$result = mysql_query($statement)) {
								return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:agentTimeOut]';
							}
							else {
								# debugging purposes <remove on production>	
									//echo "updated the database";
				
								# return the values											
									return true;
							}	
					}	
			//}
	}
	
	public function agentLogOut ($timeId) {
		$timeOut = time();
		$dateOut = date("Y-m-d", time());
		# prepare the statement
			$statement = "UPDATE agent_timein SET time_out = '$timeOut', time_outdate = '$dateOut' WHERE time_id = '$timeId'";	
		# query & check if error has occured
			if(!$result = mysql_query($statement)) {						
				return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:agentTimeOut]';
			}
			else {
				# debugging purposes <remove on production>	
					//echo "updated the database";

				# return the values											
					return true;							
			}		
	}
	
	public function finishCall ($callStatus, $callId, $ticketId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/				
				$time = time();

				# prepare the statement
					$statement = "UPDATE call_list SET call_time = '$time', call_status = '$callStatus', ticket_id = '$ticketId' WHERE call_id = '" . $callId . "'";	
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[AgentHandle:agentTimeOut]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
		
						# return the values											
							return true;
					}					
			//}
	}
}	
?>
